Methods to remove fragmentation on indexes

After you have analyzed the degree of fragmentation on the index, decide how you will defragment the index. You can either reorganize or rebuild the indexes.

Reorganizing indexes

Rebuilding indexes

  • This is the preferred method if the level of fragmentation is below 30%.

  • It's a lightweight procedure and always performed online

  • It defragments logical references.

  • Reorganizing an index will not update statistics at all.

  • If the level of fragmentation is higher than 30%, indexes should be rebuilt.

  • Rebuilding indexes can be done online only on the SQL Server Enterprise Edition. The Standard Edition does not have this option, which means rebuilding indexes will block selected database objects during defragmentation.

  • The rebuild time could be significant and may suspend certain TOS activity during defragmentation. This should be done during off-peak hours.

  • The statistics associated with that index will be updated during the rebuild.

Generally, you can ignore small indexes that are fragmented (less than 1000 pages). But if you have a very frequently-used query that relies on a smaller index, its defragmentation may save a few microseconds which may improve overall CPU load. The DBA should monitor the N4 Top SQL view and determine what to do for those small indexes.

Sometimes the terminal could be in 24/7 high load mode and has no dedicated maintenance time. In this case, rebuilding indexes may significantly impact the database.

Navis recommends the following approaches:

Often this approach alone can significantly reduce index fragmentation. This process does not hold locks for a long time so it does not generally block queries or updates that are running. Reorganizing indexes is always performed online (except if you have the SQL Server Standard Edition). After the index reorganization, you must update statistics.

You need to decide on the order and level of intensity for which index to rebuild first. Use T-SQL to rebuild indexes because it allows you to cancel the rebuild in case there is drastic impact on current activity. The index rebuild is NOT an incremental transaction. This means when you cancel it and start over, the database will start rebuilding the index from scratch. Canceling does not clear the cache so the next consecutive runs could take less time. But try not to start and cancel rebuilding an index multiple times because the rollback transaction is also costly.

When you're unable rebuild indexes but you need to improve the performance of a few queries during peak hours, updating statistics can help improve performance. It does not cause blocking and is an online operation, i.e. the tables are available while statistics are updated. It does not take a significant amount of database resources.

It is possible that two statistics exist at the same time: by index and by column. One of them could be redundant.